package cn.edu.tju.elm.repository.wallet;

import cn.edu.tju.elm.model.wallet.WalletTransaction;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Optional;

@Repository
public interface WalletTransactionRepository extends JpaRepository<WalletTransaction, Long> {
    /**
     * 根据转入或转出钱包ID查询交易记录（按时间倒序）
     */
    List<WalletTransaction> findByToWalletIdOrFromWalletIdOrderByCreateTimeDesc(Long toWalletId, Long fromWalletId);

    /**
     * 根据钱包ID分页查询交易记录
     */
    @Query("SELECT t FROM WalletTransaction t WHERE (t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND t.type <> 'FREEZE_INCOME' ORDER BY t.createTime DESC")
    Page<WalletTransaction> findByWalletId(@Param("walletId") Long walletId, Pageable pageable);

/**
     * 根据钱包ID和交易类型查询
     */
    @Query("SELECT t FROM WalletTransaction t WHERE (t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND t.type = :type ORDER BY t.createTime DESC")
    List<WalletTransaction> findByWalletIdAndType(@Param("walletId") Long walletId, @Param("type") String type);

    /**
     * 根据订单ID查询交易记录
     */
    Optional<WalletTransaction> findByOrderId(Long orderId);

    /**
     * 根据业务ID查询交易记录
     */
    Optional<WalletTransaction> findByBusinessId(Long businessId);

    /**
     * 根据交易状态查询
     */
    List<WalletTransaction> findByStatusOrderByCreateTimeDesc(String status);

    /**
     * 根据交易类型查询
     */
    List<WalletTransaction> findByTypeOrderByCreateTimeDesc(String type);

    /**
     * 根据时间范围查询交易记录
     */
    List<WalletTransaction> findByCreateTimeBetweenOrderByCreateTimeDesc(Date start, Date end);

    /**
     * 根据钱包ID和时间范围查询
     */
    @Query("SELECT t FROM WalletTransaction t WHERE (t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND t.type <> 'FREEZE_INCOME' AND t.createTime BETWEEN :start AND :end ORDER BY t.createTime DESC")
    List<WalletTransaction> findByWalletIdAndCreateTimeBetween(
            @Param("walletId") Long walletId, 
            @Param("start") Date start, 
            @Param("end") Date end);

    /**
     * 统计钱包的交易数量
     */
    @Query("SELECT COUNT(t) FROM WalletTransaction t WHERE t.toWalletId = :walletId OR t.fromWalletId = :walletId")
    long countByWalletId(@Param("walletId") Long walletId);

    /**
     * 统计时间段内指定类型的交易总金额
     */
    @Query("SELECT COALESCE(SUM(t.amount), 0) FROM WalletTransaction t WHERE " +
           "(t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND " +
           "t.type = :type AND t.createTime BETWEEN :start AND :end")
    BigDecimal sumAmountByWalletIdAndTypeAndPeriod(
            @Param("walletId") Long walletId,
            @Param("type") String type,
            @Param("start") Date start,
            @Param("end") Date end);

    /**
     * 查询钱包最近N条交易记录
     */
    @Query(value = "SELECT * FROM wallet_transaction t WHERE (t.to_wallet_id = :walletId OR t.from_wallet_id = :walletId) AND t.type <> 'FREEZE_INCOME' ORDER BY t.create_time DESC LIMIT :limit", nativeQuery = true)
    List<WalletTransaction> findRecentByWalletId(@Param("walletId") Long walletId, @Param("limit") int limit);

    /**
     * 根据交易状态和类型查询
     */
    List<WalletTransaction> findByStatusAndTypeOrderByCreateTimeDesc(String status, String type);

    /**
     * 统计今日交易数量
     */
    @Query("SELECT COUNT(t) FROM WalletTransaction t WHERE (t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND t.createTime BETWEEN :start AND :end")
    long countTodayTransactionsByWalletId(
            @Param("walletId") Long walletId,
            @Param("start") Date todayStart,
            @Param("end") Date todayEnd);

    /**
     * 查找失败的交易记录
     */
    @Query("SELECT t FROM WalletTransaction t WHERE t.status != 'SUCCESS' ORDER BY t.createTime DESC")
    List<WalletTransaction> findFailedTransactions();
    
    @Query("SELECT t FROM WalletTransaction t WHERE (t.toWalletId = :walletId OR t.fromWalletId = :walletId) AND t.createTime BETWEEN :start AND :end ORDER BY t.createTime DESC")
    List<WalletTransaction> findByCreateTimeBetweenAndWalletId(@Param("start") Date start,
    														   @Param("end") Date end,
    														   @Param("walletId") Long walletId);
}
